package cn.fornever.tools.db;

import cn.fornever.tools.db.assist.entity.*;
import cn.fornever.tools.db.assist.util.DBServiceCommonImpl;
import cn.fornever.tools.db.assist.util.DBServiceMysqlImpl;
import cn.fornever.tools.db.assist.util.DBServiceSqlServerImpl;
import cn.fornever.tools.db.assist.util.IDBService;

import java.sql.*;
import java.util.*;

/**
 * 数据库工具类.
 * <P>注意:
 * <P>本工具类中的{@link #closeConnection(Connection)} 方法是用来关闭数据库连接的.
 * 除此之外,其它任何方法均不会主动关闭数据库连接.
 *
 * @author ZhangAY
 */
public class DBUtil {

    private DBUtil() {
    }

    /**
     * 获取数据库连接.
     *
     * @param config JDBC配置信息
     * @return 数据库连接
     */
    public static Connection getConnection(JDBCConnectionConfig config) {
        String driverClass = config.getDriverClass();
        Driver driver = getDriver(driverClass);
        Properties properties = new Properties();
        properties.setProperty("user", config.getUserName());
        properties.setProperty("password", config.getPassword());
        if (config.getProperties() != null) {
            properties.putAll(config.getProperties());
        }
        Connection conn = null;
        try {
            conn = driver.connect(config.getConnectionURL(), properties);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     *
     * @param connection 数据库连接
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取数据库Meta信息.
     *
     * @param connection 数据库连接
     * @return 数据库Meta信息;如果出现异常则会返回{@code null}
     */
    public static DatabaseMetaData getDatabaseMetaData(Connection connection) {
        DatabaseMetaData metaData = null;
        try {
            metaData = connection.getMetaData();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return metaData;
    }

    /**
     * 获取所有表的信息
     * <p>注意,不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.
     *
     * @param connection       数据库连接.
     * @param catalog          数据库的catalog
     * @param schemaPattern    数据库的schema名称.
     * @param tableNamePattern 表名.
     * @param types            表类型.参考取值:"TABLE","VIEW","SYSTEM TABLE","GLOBAL TEMPORARY","LOCAL TEMPORARY","ALIAS", "SYNONYM".允许为 {@code null}
     * @return 符合要求的所有表的信息.如果出现异常, 则返回 {@code null}.
     */
    public static List<TableInfo> getTableInfo(Connection connection, String catalog, String schemaPattern,
                                               String tableNamePattern, String types[]) {

        return getDBServiceImpl(connection).getTableInfo(connection,catalog, schemaPattern, tableNamePattern, types);
    }


    /**
     * 获取所有列的信息.
     * <p>注意,不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.
     *
     * @param connection        数据库连接.
     * @param catalog           数据库的catalog
     * @param schemaPattern     数据库的schema名称.
     * @param tableNamePattern  表名.
     * @param columnNamePattern 列名.
     * @return 符合要求的所有表的信息.如果出现异常, 则返回 {@code null}.
     */
    public static List<ColumnInfo> getColoumInfo(Connection connection, String catalog, String schemaPattern,
                                                 String tableNamePattern, String columnNamePattern) {
        return getDBServiceImpl(connection).getColoumInfo(connection,catalog, schemaPattern, tableNamePattern,columnNamePattern);

    }

    /**
     * 获取所有表的主键信息
     * <p>注意,不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.
     *
     * @param connection       数据库连接.
     * @param catalog          数据库的catalog
     * @param schemaPattern    数据库的schema名称.
     * @param tableNamePattern 表名.
     * @return 符合要求的所有表的主键信息.如果出现异常, 则返回 {@code null}.
     */
    public static List<PrimaryKeyInfo> getPrimaryKeyInfo(Connection connection, String catalog, String schemaPattern,
                                                         String tableNamePattern) {
        List<PrimaryKeyInfo> result = null;
        DatabaseMetaData metaData = getDatabaseMetaData(connection);
        ResultSet tablesResultSet;
        try {
            tablesResultSet = metaData.getPrimaryKeys(catalog, schemaPattern, tableNamePattern);
            result = new ArrayList<>();
            String str;
            while (tablesResultSet.next()) {
                PrimaryKeyInfo primaryKeyInfo = new PrimaryKeyInfo();
                primaryKeyInfo.setTableCat(tablesResultSet.getString(1));//TABLE_CAT
                primaryKeyInfo.setTableSchem(tablesResultSet.getString(2));//TABLE_SCHEM
                primaryKeyInfo.setTableName(tablesResultSet.getString(3));//TABLE_NAME
                primaryKeyInfo.setColumnName(tablesResultSet.getString(4));//COLUMN_NAME
                str = tablesResultSet.getString(5);//KEY_SEQ
                if (str == null) {
                    primaryKeyInfo.setKeySeq(null);
                } else {
                    primaryKeyInfo.setKeySeq(Short.valueOf(str));
                }
                primaryKeyInfo.setPkName(tablesResultSet.getString(6));//PK_NAME
                result.add(primaryKeyInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 获取所有表的外键信息.
     * <p>注意:不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.比如,MYSQL下 schema 就是无效的.
     *
     * @param connection 数据库连接.
     * @param catalog    数据库的catalog.
     *                   <p>对通配符及null等支持如下:
     *                   <table border='2'>
     *                   <caption>对通配符及null等支持情况</caption>
     *                   <tr>
     *                   <th>数据库驱动</th>
     *                   <th>通配符</th>
     *                   <th>备注</th>
     *                   </tr>
     *                   <tr>
     *                   <th>MYSQL</th>
     *                   <th>支持%和_</th>
     *                   <th>支持null</th>
     *                   </tr>
     *                   <tr>
     *                   <th>ORACLE</th>
     *                   <th>--</th>
     *                   <th>--</th>
     *                   </tr>
     *                   </table>
     * @param schema     数据库的schema名称.
     *                   <p>对通配符及null等支持如下:
     *                   <table border='2'>
     *                   <caption>对通配符及null等支持情况</caption>
     *                   <tr>
     *                   <th>数据库驱动</th>
     *                   <th>通配符</th>
     *                   <th>备注</th>
     *                   </tr>
     *                   <tr>
     *                   <th>MYSQL</th>
     *                   <th>--</th>
     *                   <th>此字段不起作用</th>
     *                   </tr>
     *                   <tr>
     *                   <th>ORACLE</th>
     *                   <th>--</th>
     *                   <th>--</th>
     *                   </tr>
     *                   </table>
     * @param tableName  表名.
     *                   <p>对通配符及null等支持如下:
     *                   <table border='2'>
     *                   <caption>对通配符及null等支持情况</caption>
     *                   <tr>
     *                   <th>数据库驱动</th>
     *                   <th>通配符</th>
     *                   <th>备注</th>
     *                   </tr>
     *                   <tr>
     *                   <th>MYSQL</th>
     *                   <th>不支持</th>
     *                   <th>必须是完整表名,不支持null</th>
     *                   </tr>
     *                   <tr>
     *                   <th>ORACLE</th>
     *                   <th>--</th>
     *                   <th>--</th>
     *                   </tr>
     *                   </table>
     * @return 符合要求的所有表的外键信息.如果出现异常, 则返回 {@code null}.
     */
    public static List<ForeignKeyInfo> getForeignKeyInfo(Connection connection, String catalog, String schema, String tableName) {
        List<ForeignKeyInfo> result = null;
        DatabaseMetaData metaData = getDatabaseMetaData(connection);
        ResultSet tablesResultSet;
        try {
            tablesResultSet = metaData.getExportedKeys(catalog, schema, tableName);
            result = new ArrayList<>();
            String str;
            while (tablesResultSet.next()) {
                ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
                foreignKeyInfo.setPktableCat(tablesResultSet.getString(1));//PKTABLE_CAT
                foreignKeyInfo.setPktableSchem(tablesResultSet.getString(2));//PKTABLE_SCHEM
                foreignKeyInfo.setPktableName(tablesResultSet.getString(3));//PKTABLE_NAME
                foreignKeyInfo.setPkcolumnName(tablesResultSet.getString(4));//PKCOLUMN_NAME
                foreignKeyInfo.setFktableCat(tablesResultSet.getString(5));//FKTABLE_CAT
                foreignKeyInfo.setFktableSchem(tablesResultSet.getString(6));//FKTABLE_SCHEM
                foreignKeyInfo.setFktableName(tablesResultSet.getString(7));//FKTABLE_NAME
                foreignKeyInfo.setFkcolumnName(tablesResultSet.getString(8));//FKCOLUMN_NAME
                str = tablesResultSet.getString(9);//KEY_SEQ
                if (str == null) {
                    foreignKeyInfo.setKeySeq(null);
                } else {
                    foreignKeyInfo.setKeySeq(Short.valueOf(str));
                }

                str = tablesResultSet.getString(10);//UPDATE_RULE
                if (str == null) {
                    foreignKeyInfo.setUpdaterule(null);
                } else {
                    foreignKeyInfo.setUpdaterule(Short.valueOf(str));
                }

                str = tablesResultSet.getString(11);//DELETE_RULE
                if (str == null) {
                    foreignKeyInfo.setDeleterule(null);
                } else {
                    foreignKeyInfo.setDeleterule(Short.valueOf(str));
                }

                foreignKeyInfo.setFkName(tablesResultSet.getString(12));//FK_NAME
                foreignKeyInfo.setPkName(tablesResultSet.getString(13));//PK_NAME

                if (str == null) {
                    foreignKeyInfo.setDeferrability(null);
                } else {
                    foreignKeyInfo.setDeferrability(Short.valueOf(str));
                }
                result.add(foreignKeyInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 获取驱动
     *
     * @param driverClass
     * @return 驱动
     */
    private static Driver getDriver(String driverClass) {
        Driver driver = null;
        try {
            Class<?> clazz = null;
            try {
                ClassLoader cl = Thread.currentThread().getContextClassLoader();
                clazz = Class.forName(driverClass, true, cl);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            if (clazz == null) {
                clazz = Class.forName(driverClass, true, DBUtil.class.getClassLoader());
            }
            driver = (Driver) clazz.newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return driver;
    }

    private static DBType getDBType(Connection connection){
        String dbProductName;
        try {
            dbProductName = connection.getMetaData().getDatabaseProductName();
        } catch (SQLException e) {
            e.printStackTrace();
            return DBType.UNKNOWN;
        }
        switch (dbProductName){
            case "Microsoft SQL Server":{
                return DBType.SQLSERVER;
            }
            case "ORACLE":{
                return DBType.ORACLE;
            }
            default:{
                return DBType.UNKNOWN;
            }
        }
    }

    private static IDBService getDBServiceImpl(Connection connection){
        DBType dbType = getDBType(connection);
        switch (dbType){
            case SQLSERVER:{
                return DBServiceSqlServerImpl.getInstance();
            }
            case ORACLE:{
                return DBServiceSqlServerImpl.getInstance();
            }
            case MYSQL:{
                return DBServiceMysqlImpl.getInstance();
            }
            default:{
                return DBServiceCommonImpl.getInstance();
            }
        }
    }
}
